using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Common;
using RMAS.DAL;

namespace RMAS.DAL
{
    /// <summary>
    /// Summary description for QT_EXPORTBILL.
    /// </summary>
    public class QT_EXPORTBILLDao
    {
        private string _SQLSelectByKey;
        private string _SQLUpdate;
        private string _SQLInsert;
        private string _SQLDelete;
        private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion

        #region Constructor

        public QT_EXPORTBILLDao()
        {
            _dbConnection = null;
            InitSqlQuery();
        }

        public QT_EXPORTBILLDao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
        {
            string query;

            query = @"SELECT BillID, TypeBill, ExportDate, PayID, Note, TableID, PromotionID, PromotionMoney, VAT, TableMoney, ServiceMoney, OtherMoney, TotalPay, CusID, CusName, Paymented, UserName, IDPOS FROM QT_EXPORTBILL WHERE (BillID = @BillID)";
            _SQLSelectByKey = query;

            query = @"UPDATE QT_EXPORTBILL SET TypeBill = @TypeBill, ExportDate = @ExportDate, PayID = @PayID, Note = @Note, TableID = @TableID, PromotionID = @PromotionID, PromotionMoney = @PromotionMoney, VAT = @VAT, TableMoney = @TableMoney, ServiceMoney = @ServiceMoney, OtherMoney = @OtherMoney, TotalPay = @TotalPay, CusID = @CusID, CusName = @CusName, Paymented = @Paymented, UserName = @UserName, IDPOS = @IDPOS WHERE (BillID = @BillID)";
            _SQLUpdate = query;

            query = @"INSERT INTO QT_EXPORTBILL VALUES (@BillID, @TypeBill, @ExportDate, @PayID, @Note, @TableID, @PromotionID, @PromotionMoney, @VAT, @TableMoney, @ServiceMoney, @OtherMoney, @TotalPay, @CusID, @CusName, @Paymented, @UserName, @IDPOS)";
            _SQLInsert = query;

            query = @"DELETE FROM QT_EXPORTBILL WHERE (BillID = @BillID)";
            _SQLDelete = query;

            query = @"SELECT BillID, TypeBill, ExportDate, PayID, Note, TableID, PromotionID, PromotionMoney, VAT, TableMoney, ServiceMoney, OtherMoney, TotalPay, CusID, CusName, Paymented, UserName, IDPOS FROM QT_EXPORTBILL";
            _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(QT_EXPORTBILL obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                DalTools.addDbDataParameter(command, "BillID", obj.BillID, DbType.String);
                DalTools.addDbDataParameter(command, "TypeBill", obj.TypeBill, DbType.String);
                DalTools.addDbDataParameter(command, "ExportDate", obj.ExportDate, DbType.DateTime);
                DalTools.addDbDataParameter(command, "PayID", obj.PayID, DbType.Int32);
                DalTools.addDbDataParameter(command, "Note", obj.Note, DbType.String);
                DalTools.addDbDataParameter(command, "TableID", obj.TableID, DbType.String);
                DalTools.addDbDataParameter(command, "PromotionID", obj.PromotionID, DbType.String);
                DalTools.addDbDataParameter(command, "PromotionMoney", obj.PromotionMoney, DbType.Decimal);
                DalTools.addDbDataParameter(command, "VAT", obj.VAT, DbType.Double);
                DalTools.addDbDataParameter(command, "TableMoney", obj.TableMoney, DbType.Decimal);
                DalTools.addDbDataParameter(command, "ServiceMoney", obj.ServiceMoney, DbType.Decimal);
                DalTools.addDbDataParameter(command, "OtherMoney", obj.OtherMoney, DbType.Decimal);
                DalTools.addDbDataParameter(command, "TotalPay", obj.TotalPay, DbType.Double);
                DalTools.addDbDataParameter(command, "CusID", obj.CusID, DbType.String);
                DalTools.addDbDataParameter(command, "CusName", obj.CusName, DbType.String);
                DalTools.addDbDataParameter(command, "Paymented", obj.Paymented, DbType.Double);
                DalTools.addDbDataParameter(command, "UserName", obj.UserName, DbType.String);
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(QT_EXPORTBILL obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                DalTools.addDbDataParameter(command, "BillID", obj.BillID, DbType.String);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(QT_EXPORTBILL obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(QT_EXPORTBILL obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties

        private QT_EXPORTBILLList SelectListByCommand(IDbCommand command)
        {
            QT_EXPORTBILLList list = new QT_EXPORTBILLList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                QT_EXPORTBILL obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new QT_EXPORTBILL();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private QT_EXPORTBILLList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            QT_EXPORTBILLList list = new QT_EXPORTBILLList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                QT_EXPORTBILL obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new QT_EXPORTBILL();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void ReadProperties(QT_EXPORTBILL obj, IDataReader reader)
        {
            try
            {
                if (!(reader["BillID"] is DBNull))
                {
                    obj.BillID = (string)reader["BillID"];
                }
                if (!(reader["TypeBill"] is DBNull))
                {
                    obj.TypeBill = (string)reader["TypeBill"];
                }
                if (!(reader["ExportDate"] is DBNull))
                {
                    obj.ExportDate = (DateTime)reader["ExportDate"];
                }
                if (!(reader["PayID"] is DBNull))
                {
                    obj.PayID = (Int32)reader["PayID"];
                }
                if (!(reader["Note"] is DBNull))
                {
                    obj.Note = (string)reader["Note"];
                }
                if (!(reader["TableID"] is DBNull))
                {
                    obj.TableID = (string)reader["TableID"];
                }
                if (!(reader["PromotionID"] is DBNull))
                {
                    obj.PromotionID = (string)reader["PromotionID"];
                }
                if (!(reader["PromotionMoney"] is DBNull))
                {
                    obj.PromotionMoney = (decimal)reader["PromotionMoney"];
                }
                if (!(reader["VAT"] is DBNull))
                {
                    obj.VAT = (double)reader["VAT"];
                }
                if (!(reader["TableMoney"] is DBNull))
                {
                    obj.TableMoney = (decimal)reader["TableMoney"];
                }
                if (!(reader["ServiceMoney"] is DBNull))
                {
                    obj.ServiceMoney = (decimal)reader["ServiceMoney"];
                }
                if (!(reader["OtherMoney"] is DBNull))
                {
                    obj.OtherMoney = (decimal)reader["OtherMoney"];
                }
                if (!(reader["TotalPay"] is DBNull))
                {
                    obj.TotalPay = (double)reader["TotalPay"];
                }
                if (!(reader["CusID"] is DBNull))
                {
                    obj.CusID = (string)reader["CusID"];
                }
                if (!(reader["CusName"] is DBNull))
                {
                    obj.CusName = (string)reader["CusName"];
                }
                if (!(reader["Paymented"] is DBNull))
                {
                    obj.Paymented = (double)reader["Paymented"];
                }
                if (!(reader["UserName"] is DBNull))
                {
                    obj.UserName = (string)reader["UserName"];
                }
                if (!(reader["IDPOS"] is DBNull))
                {
                    obj.IDPOS = (string)reader["IDPOS"];
                }
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion

        public QT_EXPORTBILL SelectByKey(string BillID)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "BillID", BillID, DbType.String);
            QT_EXPORTBILLList list = SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public QT_EXPORTBILL SelectByKey(string BillID, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "BillID", BillID, DbType.String);
            QT_EXPORTBILLList list = SelectListByCommand(command, trans);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public QT_EXPORTBILLList SelectAll()
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            QT_EXPORTBILLList list = SelectListByCommand(command);
            return list;
        }
        public QT_EXPORTBILLList SelectAll(IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            QT_EXPORTBILLList list = SelectListByCommand(command, trans);
            return list;
        }
        public QT_EXPORTBILLList SelectByCommand(string Command)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = Command;
            QT_EXPORTBILLList list = SelectListByCommand(command);
            return list;
        }

        public QT_EXPORTBILLList SelectByCondition(QT_EXPORTBILL obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM QT_EXPORTBILL " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            QT_EXPORTBILLList list = SelectListByCommand(command, trans);
            return list;
        }

        public QT_EXPORTBILLList SelectByCondition(QT_EXPORTBILL obj)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM QT_EXPORTBILL " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            QT_EXPORTBILLList list = SelectListByCommand(command);
            return list;
        }
        private void DeleteByCondition(QT_EXPORTBILL obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM QT_EXPORTBILL " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(QT_EXPORTBILL obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            if (obj.IsSetBillID)
            {
                s += " AND BillID = @BillID ";
            }
            if (obj.IsSetTypeBill)
            {
                s += " AND TypeBill = @TypeBill ";
            }
            if (obj.IsSetExportDate)
            {
                s += " AND ExportDate = @ExportDate ";
            }
            if (obj.IsSetPayID)
            {
                s += " AND PayID = @PayID ";
            }
            if (obj.IsSetNote)
            {
                s += " AND Note = @Note ";
            }
            if (obj.IsSetTableID)
            {
                s += " AND TableID = @TableID ";
            }
            if (obj.IsSetPromotionID)
            {
                s += " AND PromotionID = @PromotionID ";
            }
            if (obj.IsSetPromotionMoney)
            {
                s += " AND PromotionMoney = @PromotionMoney ";
            }
            if (obj.IsSetVAT)
            {
                s += " AND VAT = @VAT ";
            }
            if (obj.IsSetTableMoney)
            {
                s += " AND TableMoney = @TableMoney ";
            }
            if (obj.IsSetServiceMoney)
            {
                s += " AND ServiceMoney = @ServiceMoney ";
            }
            if (obj.IsSetOtherMoney)
            {
                s += " AND OtherMoney = @OtherMoney ";
            }
            if (obj.IsSetTotalPay)
            {
                s += " AND TotalPay = @TotalPay ";
            }
            if (obj.IsSetCusID)
            {
                s += " AND CusID = @CusID ";
            }
            if (obj.IsSetCusName)
            {
                s += " AND CusName = @CusName ";
            }
            if (obj.IsSetPaymented)
            {
                s += " AND Paymented = @Paymented ";
            }
            if (obj.IsSetUserName)
            {
                s += " AND UserName = @UserName ";
            }
            if (obj.IsSetIDPOS)
            {
                s += " AND IDPOS = @IDPOS ";
            }
            if (s.Length > 0)
            {
                s = " WHERE " + s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, QT_EXPORTBILL obj)
        {
            if (obj == null) { return; }
            if (obj.IsSetBillID)
            {
                DalTools.addDbDataParameter(command, "BillID", obj.BillID, DbType.String);
            }
            if (obj.IsSetTypeBill)
            {
                DalTools.addDbDataParameter(command, "TypeBill", obj.TypeBill, DbType.String);
            }
            if (obj.IsSetExportDate)
            {
                DalTools.addDbDataParameter(command, "ExportDate", obj.ExportDate, DbType.DateTime);
            }
            if (obj.IsSetPayID)
            {
                DalTools.addDbDataParameter(command, "PayID", obj.PayID, DbType.Int32);
            }
            if (obj.IsSetNote)
            {
                DalTools.addDbDataParameter(command, "Note", obj.Note, DbType.String);
            }
            if (obj.IsSetTableID)
            {
                DalTools.addDbDataParameter(command, "TableID", obj.TableID, DbType.String);
            }
            if (obj.IsSetPromotionID)
            {
                DalTools.addDbDataParameter(command, "PromotionID", obj.PromotionID, DbType.String);
            }
            if (obj.IsSetPromotionMoney)
            {
                DalTools.addDbDataParameter(command, "PromotionMoney", obj.PromotionMoney, DbType.Decimal);
            }
            if (obj.IsSetVAT)
            {
                DalTools.addDbDataParameter(command, "VAT", obj.VAT, DbType.Double);
            }
            if (obj.IsSetTableMoney)
            {
                DalTools.addDbDataParameter(command, "TableMoney", obj.TableMoney, DbType.Decimal);
            }
            if (obj.IsSetServiceMoney)
            {
                DalTools.addDbDataParameter(command, "ServiceMoney", obj.ServiceMoney, DbType.Decimal);
            }
            if (obj.IsSetOtherMoney)
            {
                DalTools.addDbDataParameter(command, "OtherMoney", obj.OtherMoney, DbType.Decimal);
            }
            if (obj.IsSetTotalPay)
            {
                DalTools.addDbDataParameter(command, "TotalPay", obj.TotalPay, DbType.Double);
            }
            if (obj.IsSetCusID)
            {
                DalTools.addDbDataParameter(command, "CusID", obj.CusID, DbType.String);
            }
            if (obj.IsSetCusName)
            {
                DalTools.addDbDataParameter(command, "CusName", obj.CusName, DbType.String);
            }
            if (obj.IsSetPaymented)
            {
                DalTools.addDbDataParameter(command, "Paymented", obj.Paymented, DbType.Double);
            }
            if (obj.IsSetUserName)
            {
                DalTools.addDbDataParameter(command, "UserName", obj.UserName, DbType.String);
            }
            if (obj.IsSetIDPOS)
            {
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);
            }
        }
        #endregion
    }
}